import pymysql

# 与数据库交互的类
class MySQlUtil:
    def __init__(self):
        self.connect = pymysql.connect(host="localhost",
                                       user="root",
                                       password="xm123456",
                                       db="ai",
                                       charset="utf8",
                                       autocommit=True)  # 连接数据库需要的信息
        self.cur = self.connect.cursor()

    # 查询所有特征
    def selectAllFeature(self):
        # 测试查询数据
        cur = self.connect.cursor()
        select_sql = "select * from feature;"
        print("sql:\t",select_sql)
        try:
            # 执行sql
            cur.execute(select_sql)
            # 获取所有记录列表--列表包含列表
            reslults = cur.fetchall()
            cur.close()
            return reslults
        except Exception as e:
            print(e)
            print("查询出错")

    # 查询所有规则
    def selectAllRule(self):
        # 测试查询数据
        cur = self.connect.cursor()
        select_sql = "select * from rule;"
        print("sql:\t",select_sql)
        try:
            # 执行sql
            cur.execute(select_sql)
            # 获取所有记录列表--列表包含列表
            reslults = cur.fetchall()
            cur.close()
            return reslults
        except Exception as e:
            print(e)
            print("查询出错")

    # 查询所有动物
    def selectAllAnimal(self):
        # 测试查询数据
        cur = self.connect.cursor()
        select_sql = "select * from animal;"
        print("sql:\t",select_sql)
        try:
            # 执行sql
            cur.execute(select_sql)
            # 获取所有记录列表--列表包含列表
            reslults = cur.fetchall()
            cur.close()
            return reslults
        except Exception as e:
            print(e)
            print("查询出错")

    # 插入最终jielun
    def insertAnimal(self, animal):
        connect = self.connect
        cur = connect.cursor()
        insert_sql = f'insert into animal (classes) values("{animal}");'
        print("sql:\t", insert_sql)
        try:
            # 执行sql
            cur.execute(insert_sql)
            cur.close()
            return True
        except Exception as e:
            print(e)
            print("插入出错")

    # 按照主键删除特征
    def deleteFeature(self, key):
        connect = self.connect
        cur = connect.cursor()
        delete_sql = f'delete from feature where name = "{key}";'
        print("sql:\t",delete_sql)
        try:
            # 执行sql
            cur.execute(delete_sql)
            cur.close()
        except Exception as e:
            print(e)
            print("删除出错")
    # 按照主键删除规则
    def deleteRule(self, key):
        connect = self.connect
        cur = connect.cursor()
        delete_sql = f'delete from rule where id = {key};'
        print("sql:\t",delete_sql)
        try:
            # 执行sql
            cur.execute(delete_sql)
            cur.close()
        except Exception as e:
            print(e)
            print("删除出错")
    #按照主键修改规则
    def updateRule(self,key,pres,conslusion):
        connect = self.connect
        cur = connect.cursor()
        update_sql = f'update rule set antecedent = "{pres}",conclusion = "{conslusion}" where id = {key};'
        print("sql:\t",update_sql)
        try:
            # 执行sql
            cur.execute(update_sql)
            cur.close()
        except Exception as e:
            print(e)
            print("更新出错")

    # 插入规则
    def insertRule(self, id,features, answer):
        connect = self.connect
        features = str(features)
        cur = connect.cursor()
        insert_sql = f'insert into rule (id,antecedent,conclusion) values({id},"{features}","{answer}");'
        print("sql:\t",insert_sql)
        try:
            # 执行sql
            cur.execute(insert_sql)
            cur.close()
        except Exception as e:
            print(e)
            print("插入出错")

    # 插入特征--keys是一个特征列表
    def insertFeature(self, keys):
        cur = self.connect.cursor()
        for key in keys:
            if self.isExitFeature(key):
                continue
            else:
                insert_sql = f'insert into feature (name) values("{key}");'
                print("sql:\t",insert_sql)
                try:
                    # 执行sql
                    cur.execute(insert_sql)
                except Exception as e:
                    print(e)
                    print("查询出错")
        cur.close()

    # 判断特征是否存在于数据库
    def isExitFeature(self, key):
        cur = self.connect.cursor()
        select_sql = f'select name from feature where name = "{key}";'
        print("sql:\t",select_sql)
        try:
            # 执行sql
            cur.execute(select_sql)
            # 获取所有记录列表--列表包含列表
            reslults = cur.fetchall()
            cur.close()
            return (True if len(reslults) != 0 else False)
        except Exception as e:
            print(e)
            print("查询出错")